## ----measure-geographic-diffusion----

# Packages
library(data.table)
library(parallel)
library(sp)
library(sf)
library(rgdal)

# Functions
sqliteGetTable <- function(database, table) {
  require(DBI)
  require(RSQLite)
  con <- dbConnect(RSQLite::SQLite(), dbname = database)
  query <- dbSendQuery(con, paste("SELECT * FROM ", table, ";", sep="")) 
  result <- fetch(query, n = -1)
  dbClearResult(query)
  dbDisconnect(con)
  return(result)
}

countMembersByGrid <- function(i) {
  print(i)
  this_meetup_members.sp <- meetup_members.sp[meetup_members.sp$joined <= sequence[i],]
  res <- over(this_meetup_members.sp, geostat_grid.sp)
  res <- as.data.frame(table(res$GRID_ID_simp))
  res$member_density <- res$member_density <- res$Freq / sum(res$Freq)
  ita_popgrid$these_members <- res$Freq[match(ita_popgrid$GRID_ID_simp, res$Var1)]
  ita_popgrid$these_members[is.na(ita_popgrid$these_members)] <- 0
  return(data.frame(date = sequence[i], 
                    members = ita_popgrid$these_members,
                    GRID_ID_simp = ita_popgrid$GRID_ID_simp,
                    COD_REG = ita_popgrid$COD_REG))
}

load('replication_file_07_geostat_grid.RData')

region_2013.sp <- readOGR('.', 'Reg01012013_g_WGS84')
region_2013.sp <- 
  spTransform(region_2013.sp, "+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs")
region_2013.sp$COD_REG <- as.numeric(as.character(region_2013.sp$COD_REG))

geostat_grid.sp <- as(geostat_grid_simp.sf,'Spatial')
geostat_grid.sp <- 
  spTransform(geostat_grid.sp, 
              '+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs +towgs84=0,0,0')
res <- over(geostat_grid.sp, region_2013.sp)
geostat_grid.sp$COD_REG <- res$COD_REG
geostat_grid.sp$DEN_REG <- as.character(res$DEN_REG)
ita_popgrid <- geostat_grid.sp[,c('GRID_ID_simp','pop','COD_REG')]


# Calculate density from entire meetup dataset
meetup_members <- sqliteGetTable('meetup_mar15.sqlite', 'member')
meetup_members <- meetup_members[meetup_members$country %in% c("it"),]
meetup_members$joined <- as.Date(meetup_members$joined)
meetup_members.sp <- 
  SpatialPointsDataFrame(meetup_members[,c('lon','lat')],  
                         data = meetup_members,
                         proj4string = CRS('+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs'))

sequence <-
  seq(from = as.Date('2005-07-23'), 
      to = as.Date('2015-03-01'),
      by = 7)

no_cores <- detectCores() - 1
cl <- makeCluster(no_cores, type="FORK")
members_pop_grid_dt <- rbindlist(parLapply(cl, 1:length(sequence), countMembersByGrid))
stopCluster(cl)
members_pop_grid_dt$north_south <- ifelse(members_pop_grid_dt$COD_REG < 13, "North", "South")

members_pop_grid_dt$pop <- 
  ita_popgrid$pop[match(members_pop_grid_dt$GRID_ID_simp, ita_popgrid$GRID_ID_simp)]

save(members_pop_grid_dt, file = "replication_file_08_geographic_diffusion.RData")
